package com.hnkywl.sts.dao.djgl;

import com.hnkywl.sts.entity.djgl.Cgdmxx;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;
import org.hibernate.type.Type;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: jerry
 * Date: 13-5-7
 * Time: 下午8:35
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class CgdmxxDao extends SimpleHibernateTemplate<Cgdmxx, Long> {

    public List<Map> findCgmxx(Long id) {
        Criteria criteria = getSession().createCriteria(Cgdmxx.class)
                .createAlias("gg", "gg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgd", "cgd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgjsd", "jsd", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("cgd.cgdh").as("cgd.cgdh"))
                        .add(Property.forName("cgd.cglx").as("cgd.cglx"))
                        .add(Property.forName("gg.id").as("wzgg.id"))
                        .add(Property.forName("dhdStr").as("dhdStr"))
                        .add(Property.forName("gg.code").as("wzgg.code"))
                        .add(Property.forName("gg.oldcode").as("wzgg.oldcode"))
                        .add(Property.forName("gg.name").as("wzgg.name"))
                        .add(Property.forName("gg.th").as("wzgg.th"))
                        .add(Property.forName("gg.cz").as("wzgg.cz"))
                        .add(Projections.sqlProjection("gg1_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
//                        .add(Projections.sqlProjection("gg1_.cz||' '||gg1_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("gg.dw").as("wzgg.dw"))
                        .add(Property.forName("ph").as("ph"))
                        .add(Property.forName("cd.id").as("cd.id"))
                        .add(Property.forName("cd.z").as("cd.z"))
                        .add(Property.forName("ck").as("ck"))
                        .add(Property.forName("js").as("js"))
                        .add(Property.forName("sbsl").as("sbsl"))
                        .add(Property.forName("rkjs").as("rkjs"))
                        .add(Property.forName("rkjz").as("rkjz"))
                        .add(Property.forName("sku").as("sku"))
                        .add(Property.forName("rksl").as("rksl"))
                        .add(Property.forName("rkhsj").as("rkhsj"))
                        .add(Property.forName("sssl").as("sssl"))
                        .add(Property.forName("ssjs").as("ssjs"))
                        .add(Property.forName("th").as("th"))
                        .add(Property.forName("kh.id").as("kh.id"))
                        .add(Property.forName("kh.khbm").as("kh.khbm"))
                        .add(Property.forName("jzfs.id").as("jzfs.id"))
                        .add(Property.forName("jzfs.z").as("jzfs.z"))
                        .add(Property.forName("hsj").as("hsj"))
                        .add(Property.forName("bhsj").as("bhsj"))
                        .add(Property.forName("bz").as("bz"))
                        .add(Property.forName("ph").as("ph"))
                        .add(Property.forName("jsd.jsdh").as("jsdh"))
                        .add(Property.forName("jsd.id").as("jsdId"))
                        .add(Projections.sqlProjection("mxh as \"mxh\"", new String[]{"mxh"}, new Type[]{Hibernate.LONG}))
                ).add(Restrictions.eq("cgd.id", id)).addOrder(Property.forName("gg.oldcode").asc())
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        criteria.addOrder(Property.forName("mxh").asc());
        return (List<Map>) criteria.list();
    }

}
